import xlrd  # 读取excel文件
from datetime import datetime
import math
import socket
import binascii
import random
import os
import time
from xlutils3.copy import copy
from xlrd import open_workbook

# ip = "47.108.129.68"  #生产服务器
ip = "47.108.162.172"  #生产服务器
port = 8181
end = "b1 b2 b3 b4 b5 b6 b7 b8"
data_long_1 = "a1 f1 c0 c1 c2 c3 c4 c5 c6 c7 c8 c9 cA cb cc cd ce cf d0"
lastUploadTime = "2023/04/13"

# 力值参数
parameterA = 0
parameterB = 0.333333
parameterC = -98.333336

# 流量
parameterA1 = 0.00004
parameterB1 = -0.060792
parameterC1 = 42.302303

# 位移
parameterA2 = 0.000035
parameterB2 = 0.067138
parameterC2 = -0.75232

terminalNo = 3030
threshold = 345


# 数据编号


def readexcel(path):
    global lastUploadTime
    numCount = 10
    assignment = []
    excelname = os.listdir(path)  # 获取路径下所有文件的文件名
    totalData = len(excelname)
    numberOfSuccesses = 0
    name = "1"
    # print(excelname)
    for x in excelname:
        name = x
        data_F = ""
        information = {
            "projectName": "",
            "num": "",
            "start_time": "",
            "end_time": ""
        }
        backStress = []
        backeDisplacement = []
        d = os.path.join(path, x)  # 路径拼接
        date = xlrd.open_workbook(d)  # 打开目标文件
        table = date.sheets()[0]  # 打开目标文件的第一个工作薄，也可用名称代替如table=date.sheet_by_name(u'sheet1')
        b_data = table.col_values(1)  # 施工时间读取
        c_data = table.col_values(2)  # 施工力值读取
        d_data = table.col_values(3)  # 位移读取
        e_data = table.col_values(5)  # 任务编号
        try:
            time_str = datetime.strptime(b_data[3], '%Y/%m/%d %H:%M:%S')
        except ValueError:
            time_str = datetime.strptime(b_data[3].replace("-", "/"), '%Y/%m/%d %H:%M:%S')
            # time_str = datetime.strptime(b_data[3], '%Y/%m/%d %H:%M:%S')
        if type(lastUploadTime) == str:
            lastUploadTime = datetime.strptime(lastUploadTime, '%Y/%m/%d')
        if time_str > lastUploadTime:
            information["projectName"] = b_data[0]
            information["num"] = e_data[0]
            information["start_time"] = b_data[3]
            information["end_time"] = b_data[-1]
            # excel_write(information)
            assignment.append(information)
            # new_task(assignment)
            # print(constructionTime)
            y = str(hex(int(str(time_str)[2:4])))[2:4].zfill(2)
            m = str(hex(int(str(time_str)[5:7])))[2:4].zfill(2)
            d = str(hex(int(str(time_str)[8:10])))[2:4].zfill(2)
            H = str(hex(int(str(time_str)[11:13])))[2:4].zfill(2)
            M = str(hex(int(str(time_str)[14:16])))[2:4].zfill(2)
            S = str(hex(int(str(time_str)[17:19])))[2:4].zfill(2)
            data_time = S + " " + M + " " + H + " " + d + " " + m + " " + y

            stress = c_data[3:]
            stress_float = [float(i) for i in stress]
            displacement = d_data[3:]
            displacement_float = [float(i) for i in displacement]
            for y, x in zip(stress_float, displacement_float):
                p = back_data(y)
                if p >= threshold:  # 清除低功耗以下的数据
                    s = back_data1(x / 60)
                    backStress.append(round(p, 1))
                    backeDisplacement.append(round(s, 2))
            # 内存地址

            data_count_1 = hex(len(backStress) + 44)[2:].zfill(6)
            data_count_2 = data_count_1[0:2] + " " + data_count_1[2:4] + " " + data_count_1[4:6]
            # 数据编号
            numCount += 1
            data_count_3 = hex(numCount)[2:].zfill(6)
            dataNum = data_count_3[0:2] + " " + data_count_3[2:4] + " " + data_count_3[4:6]
            # print(dataNum)

            # 电池
            batteryValue = random.randint(1000, 1825)
            batteryValue_2 = (hex(batteryValue)[2:6]).zfill(4)
            # 组装力值位移数据
            for p, s in zip(backStress, backeDisplacement):
                data_s_2 = (hex(int(s)))[2:6].zfill(4)
                data_F_2 = (hex(int(p)))[2:6].zfill(4)
                data_s_3 = hex(random.randint(0, 2))[2:].zfill(2)
                data_F = data_F + " " + data_F_2[0:2] + " " + data_F_2[2:4] + " " + data_s_2[
                                                                                    0:2] + " " + data_s_2[
                                                                                                 2:4] + " " + batteryValue_2[
                                                                                                              0:2] + " " + batteryValue_2[
                                                                                                                           2:4] + " " + "00" + " " + data_s_3

            # 压力位移数据
            data_F = data_F + " " + end
            # 终端编号
            terminal_Num = (hex(terminalNo))[2:6].zfill(4)
            # 数据长度
            data_long_2 = hex(len(backStress) * 8 + 42)[2:].zfill(6)
            # print(hex(len(backStress)*8+42),"数据长度")
            # print(data_long_2,"数据总长")
            data_long_3 = data_long_2[2:4].zfill(2)
            # print(data_long_3)
            data_long_4 = data_long_2[4:6].zfill(2)
            # print(data_long_4)
            data_long = data_long_3 + " " + data_long_4 + " " + data_long_1
            # 内存地址
            a = (hex(random.randint(1, 100))[2:4]).zfill(2)
            b = (hex(random.randint(1, 100))[2:4]).zfill(2)
            c = (hex(random.randint(1, 100))[2:4]).zfill(2)
            data_start = a + " " + b + " " + c

            data_final = terminal_Num[0:2] + " " + terminal_Num[
                                                   2:4] + " " + data_long + " " + data_time + " " + data_start + " " + data_count_2 + " " + dataNum + data_F
            numberOfSuccesses = sendData(data_final, numberOfSuccesses, name)
        else:
            print(b_data[3])
        print("数据总数：{}，成功数量{}".format(totalData, numberOfSuccesses))
        time.sleep(0.1)
    new_assignment=merge_tasks(assignment)
    excel_write(new_assignment)



def back_data(delta):
    if parameterA != 0:
        first_root = (-parameterB - math.sqrt(parameterB ** 2 - (4 * parameterA * (parameterC - delta)))) / (
                2 * parameterA)
        second_root = (-parameterB + math.sqrt(parameterB ** 2 - (4 * parameterA * (parameterC - delta)))) / (
                2 * parameterA)
        if second_root > first_root:
            return second_root
        else:
            return first_root
    else:
        first_root = (delta - parameterC) / parameterB
        return first_root


# 反推位移数据
def back_data1(delta):
    first_root = (-parameterB2 - math.sqrt(parameterB2 ** 2 - (4 * parameterA2 * (parameterC2 - delta)))) / (
            2 * parameterA2)
    second_root = (-parameterB2 + math.sqrt(parameterB2 ** 2 - (4 * parameterA2 * (parameterC2 - delta)))) / (
            2 * parameterA2)
    if second_root > first_root:
        delta = second_root
    else:
        delta = first_root
    if parameterB1 ** 2 - (4 * parameterA1 * (parameterC1 - delta)) > 0:
        first_root1 = (-parameterB1 - math.sqrt(parameterB1 ** 2 - (4 * parameterA1 * (parameterC1 - delta)))) / (
                2 * parameterA1)
        second_root1 = (-parameterB1 + math.sqrt(parameterB1 ** 2 - (4 * parameterA1 * (parameterC1 - delta)))) / (
                2 * parameterA1)
        if second_root1 > first_root1:
            return second_root1
        else:
            return first_root1
    else:
        return 0


def sendData(data_final, numberOfSuccesses, excelname):
    print("发送数据", data_final)
    print(excelname)
    try:
        client = socket.socket()
        client.connect((ip, port))
        client.send(bytes.fromhex(data_final))
        # time.sleep(2)
        con = client.recv(1024)
        hex_data = binascii.b2a_hex(con).decode('utf-8')[4:6]
        print(binascii.b2a_hex(con).decode('utf-8'))
        if hex_data != "":
            int_data = int(hex_data, 16)
            if int_data == 1:
                print(type(numberOfSuccesses))
                print(numberOfSuccesses)
                numberOfSuccesses += 1
                print("校验正确")
                return numberOfSuccesses

        if hex_data == "":
            print("校验失败")
    except Exception as exc:
        print(exc)
        return numberOfSuccesses


def excel_write(new_assignment):
    # print("4", "=", expect_data["monitorValue"])
    path = os.getcwd()
    path = os.path.join(path, "assignment.xls")
    # 读取excel文件
    r_xls = open_workbook(path)
    # fcntl.flock(r_xls.fileno(), fcntl.LOCK_EX)
    # 获取已有行数
    row = r_xls.sheets()[0].nrows
    excel = copy(r_xls)
    worksheet = excel.get_sheet(0)
    # print(information)
    # 写入数据
    for i , information in enumerate(new_assignment):
        for x, y in enumerate(information.values()):
            worksheet.write(row+i, x, y)
        # 保存
        excel.save(path)  # 文件保存在项目的excels目录内


def merge_tasks(assignment):
    record_dict = {}
    merge_keys = ["start_time", "end_time"]

    # 遍历所有字典，将"projectName"项重复的值和"start_time"、"end_time"键的值添加到记录字典中
    for d in assignment:
        num_value = d.get("num")
        try:
            # 尝试使用 '%Y-%m-%d %H:%M:%S' 格式解析日期字符串
            starttime = datetime.strptime(d.get("start_time"), '%Y-%m-%d %H:%M:%S')
            endtime = datetime.strptime(d.get("end_time"), '%Y-%m-%d %H:%M:%S')
        except ValueError:
            # 如果解析失败，则尝试使用 '%Y/%m/%d %H:%M:%S' 格式解析日期字符串
            starttime = datetime.strptime(d.get("start_time"), '%Y/%m/%d %H:%M:%S')
            endtime = datetime.strptime(d.get("end_time"), '%Y/%m/%d %H:%M:%S')
        if num_value not in record_dict:
            record_dict[num_value] = {"count": 1, "start_time": starttime, "end_time": endtime}
        else:
            record_dict[num_value]["count"] += 1
            record_dict[num_value]["start_time"] = min(record_dict[num_value]["start_time"],
                                                               starttime)
            record_dict[num_value]["end_time"] = max(record_dict[num_value]["end_time"], endtime)

    new_assignment = []
    # 对记录字典中的每个"projectName"项重复的值，查找所有"projectName"值等于该值的字典并进行合并替换
    for num_value, merge_values in record_dict.items():
        if merge_values["count"] > 1:
            matched_dicts = [d for d in assignment if d.get("num") == num_value]
            new_dict = {}
            for key, value in matched_dicts[0].items():
                new_dict[key] = value
            for merge_key in merge_keys:
                if merge_key == "start_time":
                    new_dict[merge_key] = merge_values[merge_key].strftime('%Y/%m/%d %H:%M:%S')
                elif merge_key == "end_time":
                    new_dict[merge_key] = merge_values[merge_key].strftime('%Y/%m/%d %H:%M:%S')
            for d in matched_dicts[1:]:
                assignment.remove(d)
            assignment.remove(matched_dicts[0])
            new_assignment.append(new_dict)
        else:
            new_assignment.append(matched_dicts[0])
    print(new_assignment)
    return new_assignment

    # print(assignment)


inn = r"G:\桌面\广西设计集团数据2023.4.26"
excel = readexcel(inn)
